package DBLayer;
import java.util.*;
import ModelLayer.*;
import java.sql.*;

public class DBSupplier implements IFDBSupplier
{
	private Connection con;
	
	public DBSupplier()
	{
	      con = DBConnection1.getInstance().getDBcon();
	}
	
	//method to build the query
	private String buildQuery(String wClause)
	{
		String query="SELECT SuppID, Name, Address, Country, PhoneNo, E_Mail FROM Supplier";
		
		if (wClause.length()>0)
		{
			query=query+" WHERE "+ wClause;
		}
		return query;
	}
	
	//method to build an employee object
	private Supplier buildSupplier(ResultSet results)
	{
		Supplier suppObj=new Supplier();
		
		try
		{
			suppObj.setId(results.getInt("SuppID"));
			suppObj.setName(results.getString("Name"));
			suppObj.setAddress(results.getString("Address"));
			suppObj.setCountry(results.getString("Country"));
			suppObj.setPhoneNo(results.getString("PhoneNo"));
			suppObj.setEmail(results.getString("E_Mail"));
		}
		catch(Exception e)
		{
			System.out.println("Error in building the supplier object");
		}
		
		return suppObj;
	}
	
	//michWere is used whenever we want to select more than one supplier
	private ArrayList<Supplier> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<Supplier> list = new ArrayList<Supplier>();
		String query =  buildQuery(wClause);
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			while( results.next() )
			{
				Supplier suppObj=new Supplier();
				suppObj = buildSupplier(results);
				list.add(suppObj);
			}
			stmt.close();
		}
		catch(Exception e)
		{
			System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}
		return list;
	}
	
	//Singelwhere is used when we only select one supplier 
	private Supplier singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Supplier suppObj=new Supplier();
		
		String query =  buildQuery(wClause);
		System.out.println(query);
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if( results.next() )
			{
				suppObj=buildSupplier(results);
				stmt.close();
			}
		}
		catch(Exception e)
		{
			System.out.println("Query exception: "+e);
		}
		return suppObj;
	}
	
	@Override
	//get all the suppliers
	public ArrayList<Supplier> getAllSupp(boolean retrieveAssociation)
	{
		return miscWhere("", retrieveAssociation);
	}

	@Override
	//get supplier by his id
	public Supplier getSuppById(int id, boolean retrieveAssociation)
	{
		String wClause = "  suppID = '" + id + "'";
		return singleWhere(wClause, retrieveAssociation);
	}

	@Override
	//get supplier by his name
	public Supplier getSupplierByName(String name, boolean retrieveAssociation)
	{
		String wClause = "Name = '" + name + "'";
		return singleWhere(wClause, retrieveAssociation);
	}

	@Override
	//insert a supplier into the db
	public int insertSupplier(Supplier suppObj) throws Exception
	{	
		int result = -1;
		
		String query="INSERT INTO Supplier(Name, Address, Country, PhoneNo, E_Mail) VALUES('"+
				suppObj.getName() + "','"+
				suppObj.getAddress() + "','"+
				suppObj.getCountry() + "','"+
				suppObj.getPhoneNo()+ "','"+
				suppObj.getEmail() + "')";
		
		System.out.println("insert : " + query);
	    try
	    {
	    	Statement stmt = con.createStatement();
	    	stmt.setQueryTimeout(5);
	    	result = stmt.executeUpdate(query);
	    	stmt.close();
	    }
	    catch(SQLException ex)
	    {
	    	System.out.println("Supplier has not been created.");
	    	throw new Exception ("Supplier has not been inserted correctly.");
	    }
	    return(result);
	}

	@Override
	//update supplier by his ID
	public int updateSupplierByID(Supplier suppObj) 
	{
		Supplier supplier=suppObj;
		
		String query="UPDATE Supplier SET "+
				" Name= '" + supplier.getName() + "'," +
		" Address= '" + supplier.getAddress() + "'," +
				" Country= '" + supplier.getCountry() + "'," +
		" PhoneNo= '" + supplier.getPhoneNo() + "'," +
				" E_Mail= '" + supplier.getEmail() + "' " +
		" WHERE SuppID= '" + supplier.getId() + "'";
		
		int result=-1;
		System.out.println("Update query:" + query);
		
		try
		{ // update supplier
			Statement stmt = con.createStatement();		
	 		stmt.setQueryTimeout(5);
	 	 	result = stmt.executeUpdate(query);
	 	 	stmt.close();
		}
		catch(Exception ex)
		{
			System.out.println("Update exception in Supplier DB: "+ex);
		}
		return(result);
	}				

	@Override
	//update supplier by his name
	public int updateSupplierByName(Supplier suppObj)
	{
		Supplier supplier=suppObj;
		
		String query="UPDATE Supplier SET "+
		//" SuppID= '" + supplier.getSuppId() + "'," +
				" Name= '" + supplier.getName() + "'," +
		" Address= '" + supplier.getAddress() + "'," +
				" Country= '" + supplier.getCountry() + "'," +
		" PhoneNo= '" + supplier.getPhoneNo() + "'," +
				" E_Mail= '" + supplier.getEmail() + "' " +
		" WHERE Name= '" + supplier.getName() + "'";
		
		int result=-1;
		System.out.println("Update query:" + query);
		
		try
		{ // update supplier
			Statement stmt = con.createStatement();		
	 		stmt.setQueryTimeout(5);
	 	 	result = stmt.executeUpdate(query);
	 	 	stmt.close();
		}
		catch(Exception ex)
		{
			System.out.println("Update exception in Supplier DB: "+ex);
		}
		return(result);
	}

	@Override
	//delete supplier by his name
	public int deleteSupplierByID(int id)
	{
		int result=-1;
		  
	  	String query="DELETE FROM Supplier WHERE SuppId = '" + id + "'";
	  	System.out.println(query);
	  	try
	  	{ // delete from Supplier
	  		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	result = stmt.executeUpdate(query);
	 	  	stmt.close();	  		
	  	}
	  	catch(Exception ex)
	  	{
	  		System.out.println("Delete exception in Supplier DB: "+ex);
	  	}
	  	return(result);
	}

	@Override
	//delete supplier by his id
	public int deleteSupplierByName(String name)
	{
		int result=-1;
		  
	  	String query="DELETE FROM Supplier WHERE Name = '" + name + "'";
	  	System.out.println(query);
	  	try
	  	{ // delete from Supplier
	  		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	result = stmt.executeUpdate(query);
	 	  	stmt.close();	  		
	  	}
	  	catch(Exception ex)
	  	{
	  		System.out.println("Delete exception in Supplier DB: "+ex);
	  	}
	  	return(result);
	}
}
